package com.zhiyou.dao;

import com.zhiyou.model.Contract;
import com.zhiyou.model.House;
import com.zhiyou.model.Lessee;
import com.zhiyou.model.Rent;
import com.zhiyou.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class RentDao {
    public int getRentNum(HashMap<String,String> keywordMap) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String keyword1 = keywordMap.get("keyword1");
        String keyword2 = keywordMap.get("keyword2");

        int total=0;
        String sql="select count(rid) total from rent where rpayTime between ? and ?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword1 == null||keyword1.equals("")? "2000-01-01":keyword1);
            ps.setString(2,keyword2 == null||keyword2.equals("")? "3000-01-01":keyword2);
            System.out.println("日志 : 查个数sql"+ps.toString());
            rs=ps.executeQuery();
            while (rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return total;
    }

    public List<Rent> getRent(int pageNo, int pageSize,HashMap<String,String> keywordMap) {
        ArrayList<Rent> rents = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String keyword1 = keywordMap.get("keyword1");
        String keyword2 = keywordMap.get("keyword2");

        String sql="select rid,rhid,rlid,rprice,rpayTime,haddress,lname from rent r "
                +" inner join house h on r.rhid=h.hid"
                +" inner join lessee l on r.rlid=l.lid"
                +" where rpayTime between ? and ?"
                +" limit ?,?";
        try {
            ps=conn.prepareStatement(sql);

            ps.setString(1,keyword1 == null||keyword1.equals("")? "2000-01-01":keyword1);
            ps.setString(2,keyword2 == null||keyword2.equals("")? "3000-01-01":keyword2);
            ps.setInt(3,(pageNo-1)*pageSize);
            ps.setInt(4,pageSize);
            System.out.println("日志 : 查全部sql"+ps.toString());
            rs = ps.executeQuery();
            while(rs.next()){
                Rent rent = new Rent();
                rent.setRid(rs.getInt("rid"));
                rent.setRhid(rs.getInt("rhid"));
                rent.setRlid(rs.getInt("rlid"));
                rent.setRprice(rs.getDouble("rprice"));
                rent.setRpayTime(rs.getDate("rpayTime"));
                House house = new House();
                house.setHaddress(rs.getString("haddress"));
                rent.setHouse(house);
                Lessee lessee = new Lessee();
                lessee.setLname(rs.getString("lname"));
                rent.setLessee(lessee);
                rents.add(rent);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return rents;
    }

    public void deleteById(int rid) {
        Connection conn = DBUtil.getConn();
        String sql="delete from rent where rid=?";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,rid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public void addNew(Rent rent) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="insert into rent(rhid,rlid,rprice,rpayTime) values(?,?,?,?) ";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,rent.getRhid());
            ps.setInt(2,rent.getRlid());
            ps.setDouble(3,rent.getRprice());
            ps.setDate(4,new java.sql.Date(rent.getRpayTime().getTime()));
            System.out.println("日志: sql"+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public Rent selectById(int rid) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        Rent rent=null;
        String sql="select * from rent where rid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,rid);
            rs=ps.executeQuery();
            while (rs.next()){
                rent = new Rent();
                rent.setRid(rs.getInt("rid"));
                rent.setRhid(rs.getInt("rhid"));
                rent.setRlid(rs.getInt("rlid"));
                rent.setRprice(rs.getDouble("rprice"));
                rent.setRpayTime(rs.getDate("rpayTime"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return rent;
    }

    public void updateNew(Rent rent) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="update rent set rhid=?,rlid=?,rprice=?,rpayTime=? where rid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,rent.getRhid());
            ps.setInt(2,rent.getRlid());
            ps.setDouble(3,rent.getRprice());
            ps.setDate(4,new java.sql.Date(rent.getRpayTime().getTime()));
            ps.setInt(5,rent.getRid());
            System.out.println("日志 : sql="+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }
}
